﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
using MySql.Data.MySqlClient;

namespace AsianSocietyPictureUpload
{
    partial class AsianSocietyUploadPicturesToDatabase
    {
        private void UpdateSatelliteImages()
        {
            int i = 0;

            for (int t = 0; t < 10; t++)
            {
                Thread.Sleep(1000);

                if (dtSataInfo.Rows.Count > 0)
                {
                    for (i = 0; i < dtSataInfo.Rows.Count; i++)
                    {
                        try
                       {
                            double CityHour = 0;
                            int CityID = Convert.ToInt32(dtSataInfo.Rows[i]["cityID"]);

                            CityHour = Convert.ToDouble(dtSataInfo.Rows[i]["timezone"]);

                            DateTime currentTime = DateTime.UtcNow.AddHours(CityHour);


                            if(!sateCheckExitMID(CityID, currentTime) && currentTime.Hour >= 17)
                            {
                                //InsertDBLog(i);
                               // System.Windows.Forms.MessageBox.Show(CityID.ToString());

                                Image rawImage;

                                rawImage = FetchImage(i, 1, currentTime);
                                rawImage = CropImage(rawImage);
                                rawImage = ResizeImage(rawImage);
                                rawImage = TextOverLayImageForRawSatellite(CityID, rawImage);
                                string sataNamePath = currentTime.ToString("MM_dd_yyyy_") + GetCitySatelliteName(CityID) + "1.jpg";
                                rawImage = SaveImageToLocalFolderSatellite(rawImage, sataNamePath);
                               // UploadDBLog(i, 2, "Successful");
                                rawImage.Dispose();
                                rawImage = null;



                                rawImage = FetchImage(i, 2, currentTime);
                                rawImage = CropImage(rawImage);
                                rawImage = ResizeImage(rawImage);
                                rawImage = TextOverLayImageForRawSatellite(CityID, rawImage);
                                string sataNamePath2 = currentTime.ToString("MM_dd_yyyy_") + GetCitySatelliteName(CityID) + "2.jpg";
                                rawImage = SaveImageToLocalFolderSatellite(rawImage, sataNamePath2);
                                //UploadDBLog(i, 2, "Successful");
                                rawImage.Dispose();
                                rawImage = null;

                                //string filePath = "C:\\AsianSocietyUploadedPictures\\Satellite\\" + DateTime.Now.ToString("MM_dd_yyyy_") + dtSataInfo.Rows[i]["CityName"] + dtSataInfo.Rows[i]["Value"];

                                InsertSatelliteToDatabase(CityID, sataNamePath, sataNamePath2, currentTime);

                              //  dtSataInfo.Rows[i].Delete();
 
                                Thread.Sleep(3000);
                            }
                        }
                        catch (Exception e)
                        {
                            string eString = e.ToString();
                            if (eString.Length > 100)
                                eString = eString.Substring(0, 100);
                           // UploadDBLog(i, 3, eString);
                        }
                    }
                }
                //dtSataInfo.AcceptChanges();
                //UpdateCityURL();
            }

         //   GetCoordinates2();
        }

        private int GetCityHour(string p)
        {
            foreach (DataRow row in dtSataInfo.Rows)            
                if (p.Equals(row["CityName"].ToString()))               
                    return Convert.ToInt32(row["Time"]);             
            
            return 0;
        }

        private bool sateCheckExitMID(int cityID, DateTime cityTime)
        {
            MySqlConnection mySQLCon = new MySqlConnection(setString.chinaairConnectionString);
            string sqlComand = "select mid from chinaair.tb_city_pollution where fcityid =" + cityID + " and zdate = '" + cityTime.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) + "'";

            mySQLCon.Open();

            MySqlCommand checkMID = new MySql.Data.MySqlClient.MySqlCommand(sqlComand, mySQLCon);

            MySqlDataReader dr = checkMID.ExecuteReader();


            int MID = 0;


            while (dr.Read())
            {
                MID = Convert.ToInt32(dr["mid"]);
            }

            dr.Close();



            if (MID == 0)
            {
                mySQLCon.Close();
                return true;
            }
            else
            {
                sqlComand = "select comimg1, comimg2 from tb_city_pollution where mid =" + MID;
                MySqlCommand checkImage = new MySqlCommand(sqlComand, mySQLCon);
                MySqlDataReader dr2 = checkImage.ExecuteReader();
                string sataImageName="";
                string sataImageName2="";

                while (dr2.Read())
                {
                    sataImageName = dr2["comimg1"].ToString();
                    sataImageName2 = dr2["comimg2"].ToString();
                }
                
                dr2.Close();


                if (sataImageName.Length > 5 && sataImageName2.Length > 5)
                    return true;
                else
                    return false;
            }



        }


        private void InsertSatelliteToDatabase(int cityID, string sataPath1, string sataPath2, DateTime currentTime)
        {
            MySqlConnection mySQLCon = new MySqlConnection(setString.chinaairConnectionString);
            string sqlComand = "select mid from chinaair.tb_city_pollution where fcityid =" + cityID + " and zdate = '" + currentTime.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) + "'";
            
            mySQLCon.Open();

            MySqlCommand checkMID = new MySql.Data.MySqlClient.MySqlCommand(sqlComand, mySQLCon);

            MySqlDataReader dr = checkMID.ExecuteReader();


            int MID = 0;
            

            while (dr.Read())
            {
                MID = Convert.ToInt32(dr["mid"]);
            }

            dr.Close();

            if (MID > 0)
            {
                MySql.Data.MySqlClient.MySqlCommand updateMainImage = new MySql.Data.MySqlClient.MySqlCommand("UPDATE chinaair.tb_city_pollution  SET  comimg1 = 'display/sate/" + sataPath1 + "',comimg2 = 'display/sate/" + sataPath2 + "', zenable = 'Y' WHERE mid = " + MID);

                updateMainImage.Connection = mySQLCon;
                updateMainImage.ExecuteNonQuery();
            }
            else
            {

                AsiaDBTableAdapters.tb_city_pollutionTableAdapter cityPolluA = new AsiaDBTableAdapters.tb_city_pollutionTableAdapter();

                cityPolluA.Insert("Y",
                                    DateTime.Now.Date,
                                    cityID,
                                    "",
                                    "",
                                    "",
                                    "",
                                    "",
                                    "",
                                    "",
                                    sataPath1,
                                    sataPath2,
                                    3,
                                    1,
                                    currentTime.Date,
                                    5,
                                    currentTime.Date,
                                    0,
                                    0);
            }
            mySQLCon.Close();

        }

        private void UpdateCityURL()
        {
            {
                SqlConnection myConnection = new SqlConnection();
                myConnection.ConnectionString = "Data Source=HIPPO;Initial Catalog=AsiaSociety;Integrated Security=True";
                myConnection.Open();

                string SQL = "SELECT COUNT(*) FROM [AsiaSociety].[dbo].[UpdatePhotoLogData] WHERE (SELECT CONVERT(char(10),LogDate,126)) = (SELECT CONVERT(char(10), GetDate(),126)) And StatusLevelID = 3";

                SqlCommand MyCommand = new SqlCommand(SQL, myConnection);

                int CityLeft = (int)MyCommand.ExecuteScalar();

               // if (!(CityLeft > 0))
                 //   SetCityUrl();

                myConnection.Close();
            } 
        }

        private void InsertDBLog(int row)
        {
            dateInsert = DateTime.Now;
            string LogDescription = dtSataInfo.Rows[row]["CityName"].ToString() + " - (" + dtSataInfo.Rows[row]["Path"].ToString() + ") - Sent to Retrieve Picture";
            int StatusLevelID = 1;

            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = "Data Source=HIPPO;Initial Catalog=AsiaSociety;Integrated Security=True";
            myConnection.Open();

            string SQL = "INSERT INTO [AsiaSociety].[dbo].[UpdatePhotoLogData] ([LogDate],[StatusLevelID],[LogDescription]) VALUES (@LogDate, @StatusLevelID, @LogDescription)";

            SqlCommand sqlCmd = new SqlCommand(SQL, myConnection);

            sqlCmd.Parameters.Add("@LogDate", SqlDbType.DateTime);
            sqlCmd.Parameters["@LogDate"].Value = dateInsert;
            sqlCmd.Parameters.Add("@StatusLevelID", SqlDbType.Int);
            sqlCmd.Parameters["@StatusLevelID"].Value = StatusLevelID;
            sqlCmd.Parameters.AddWithValue("@LogDescription", LogDescription);

            sqlCmd.ExecuteNonQuery();

            myConnection.Close();
        }

        public Image FetchImage(int row , int index, DateTime currentTime)
        {
            if(index > 1)
                url = urlGlobal + dtSataInfo.Rows[row]["SateName"].ToString() + GetYear(currentTime) + GetNumberOfDays(currentTime) + dtSataInfo.Rows[row]["sateTwo"];
            else
                url = urlGlobal + dtSataInfo.Rows[row]["SateName"].ToString() + GetYear(currentTime) + GetNumberOfDays(currentTime) + dtSataInfo.Rows[row]["sateOne"];

            Uri uri = new Uri(url);
            fileName = Path.GetFileName(uri.LocalPath);

            return DownloadImage(url);
        }

        private int GetNumberOfDays(DateTime currentTime)
        {
            DateTime d1 = new DateTime(currentTime.Year, 1, 1);
            DateTime d2 = currentTime;
            TimeSpan diff = d2 - d1;
            int days = diff.Days;

            return (days + 1);
        }

        private int GetYear(DateTime currentTime)
        {
            DateTime GetYear = currentTime;
            int year;
            year = GetYear.Year;

            return year;
        }

        private Image CropImage(Image rawImage)
        {
            Bitmap bmpImage = new Bitmap(rawImage);

            double oldMargin = bmpImage.Height / (double)bmpImage.Width;
            double wantedMargin = 632 / (double)980;
            double newMargin;

            if (oldMargin > wantedMargin)
            {
                newMargin = oldMargin - wantedMargin;
            }
            else
            {
                newMargin = wantedMargin - oldMargin;
            }

            int x = Convert.ToInt32(bmpImage.Width);
            int y = Convert.ToInt32(bmpImage.Height * (1 - newMargin));

            Rectangle cropArea = new Rectangle(0, 0, x, y);
        
            Bitmap bmpCrop = bmpImage.Clone(cropArea, bmpImage.PixelFormat);
            bmpImage.Dispose();
            bmpCrop.SetResolution(72.0F, 72.0F);

            return (Image)(bmpCrop);
        }

     

        private Image SaveImageToLocalFolderSatellite(Image rawImage, string sataImageName)
        {

            ImageCodecInfo jgpEncoder = GetEncoder(ImageFormat.Jpeg);

            System.Drawing.Imaging.Encoder myEncoder = System.Drawing.Imaging.Encoder.Quality;

            EncoderParameters myEncoderParameters = new EncoderParameters(1);

            EncoderParameter myEncoderParameter = new EncoderParameter(myEncoder, 90L);
            myEncoderParameters.Param[0] = myEncoderParameter;

            if (!System.IO.Directory.Exists(satellitePath))
                System.IO.Directory.CreateDirectory(satellitePath);



            if (rawImage != null)
                rawImage.Save(satellitePath + sataImageName.Replace(" - ", string.Empty), jgpEncoder, myEncoderParameters);

            return rawImage;
        }

        private Image DownloadImage(string _URL)
        {
       //     Image _tmpImage = null;
            Image webImage;

            try
            {
                System.Net.HttpWebRequest requestPic = (System.Net.HttpWebRequest)System.Net.HttpWebRequest.Create(_URL);

                System.Net.WebResponse responsePic = requestPic.GetResponse();

                webImage = Image.FromStream(responsePic.GetResponseStream());
            }
            catch (Exception e)
            {
                return null;
            }

            return webImage;
        }

        private void UploadDBLog(int row, int status, string description)
        {
            DateTime date = dateInsert;
            string LogDescription = dtSataInfo.Rows[row]["CityName"].ToString() + " - (" + dtSataInfo.Rows[row]["Path"].ToString() + dtSataInfo.Rows[row]["Value"].ToString() + ") -" + description;
            int StatusLevelID = status;

            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = "Data Source=HIPPO;Initial Catalog=AsiaSociety;Integrated Security=True";
            myConnection.Open();

            string SQL = "UPDATE [AsiaSociety].[dbo].[UpdatePhotoLogData] SET StatusLevelID = @StatusLevelID, LogDescription = @LogDescription WHERE LogDate = @LogDate";

            SqlCommand sqlCmd = new SqlCommand(SQL, myConnection);

            sqlCmd.Parameters.Add("@LogDate", SqlDbType.DateTime);
            sqlCmd.Parameters["@LogDate"].Value = date;
            sqlCmd.Parameters.Add("@StatusLevelID", SqlDbType.Int);
            sqlCmd.Parameters["@StatusLevelID"].Value = StatusLevelID;
            sqlCmd.Parameters.AddWithValue("@LogDescription", LogDescription);

            sqlCmd.ExecuteNonQuery();

            myConnection.Close();
        }
    }
}
